<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
namespace Admin\Controller;

use Think\Controller;
use Think\Vender;

class ExcelController extends CommonController {

    /**
     * 导出Excel
     * @param type $data 须导出的数据，
     * @param type $title 列名 eg. array('ID', '名称', '性别')
     * @param type $filename 导出文件名
     */
    public function exportData($data = array(), $title = array(), $filename = 'report') {
        Vendor('PHPExcel.PHPExcel');
        $objPHPExcel = new \PHPExcel();
       
        $setSheet = $objPHPExcel->getActiveSheet();

        //包含数据的最大列
        $allColumn = chr(65 + count($data[0]));
        $key_array = array_keys($data[0]);
        //包含数据的最大行
        $allRow = count($data) + 2;
        if (is_array($title[0])) {
            $key_array = $title[1];
            $title = $title[0];
        }

        $i = 'A';
        $j = 1;
        foreach ($title as $v) {
            $objPHPExcel->getActiveSheet()->setCellValue($i . '1', $v);
            $objPHPExcel->getActiveSheet()->getStyle($i . '1')->getFont()->setBold(true); //第一行加粗
            $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setWidth(18); //列宽
            $objPHPExcel->getActiveSheet()->getRowDimension($j)->setRowHeight(20); //行高
            $j++;
            $i++;
        }
        for ($currentRow = 2; $currentRow < $allRow; $currentRow++) {
            for ($currentCol = 'A', $i = 0; $currentCol < $allColumn; $currentCol++) {
                $setSheet->setCellValueExplicit($currentCol . $currentRow, $data[$currentRow - 2][$key_array[$i]], \PHPExcel_Cell_DataType::TYPE_STRING);
                $setSheet->getStyle($currentCol . $currentRow)->getNumberFormat()->setFormatCode("@");
                $setSheet->getRowDimension($currentRow)->setRowHeight(20);
                $i++;
            }
        }

        /*         * *****重新保存excel----start**************** */
        $objPHPExcel->getActiveSheet()->setTitle('Sheet1');
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header("Content-Transfer-Encoding:utf-8");
        header("Pragma: no-cache");
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '.xls');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        /*         * *****重新保存excel-----end**************** */
    }

    /**
     * 志愿者导入
     * @param string $path
     * @param array $feild 字段名称
     * @return array 数组
     */
    public function userImport($path = '', $feild, $teamId) {
        //$feild = array('ad', 'pwd', 'name', 'idCard', 'phone', 'email');
        //$path = 'Public/UExcel/test.xls';
        Vendor('PHPExcel.PHPExcel'); //导入thinkphp第三方类库

        $fType = strrchr($path, '.') == '.xls' ? 'Excel5' : 'Excel2007';
        //指定Excel类型，创建一个reader   
        $objReader = PHPExcel_IOFactory::createReader($fType);


        //设置只读取数据，不包括公式和格式   
        $objReader->setReadDataOnly(true);
        //只读取指定的sheet   
        $objReader->setLoadSheetsOnly('Sheet1');
        $objPHPExcel = $objReader->load($path);

        $curSheet = $objPHPExcel->getSheet(0);
        $setSheet = $objPHPExcel->setActiveSheetIndex(0);

        //包含数据的最大列
        $allColumn = chr(65 + count($feild) - 1);
        //包含数据的最大行  
        $allRow = $curSheet->getHighestRow();
        $data = array();
        $errData = array();
        $userData = array();
        $dataList = array();
        $gc = M('Volunteer');
        $teamVolunteer = M('Individual_team');
        //时间超过7天未激活  删除
        $importWhere['volunteerStatus'] = array(array('eq', 1), array('eq', 6), 'or');
		$importWhere['teamId'] = $_SESSION['id'];
        $importData = $gc->join('vo_individual_team team ON vo_volunteer.id = team.volunteerId')->field('vo_volunteer.*,vo_volunteer.crttime as uctime,team.*')->where($importWhere)->select();
        foreach ($importData as $k => $v) {
           if(strtotime(date('Y-m-d H:i:s', strtotime("-7 day")))> strtotime($v['uctime'])) {
                $gc->where(array('id'=>$v['volunteerId']))->delete();
				$teamVolunteer->where(array('volunteerId'=>$v['volunteerId']))->delete();
            }
        }
        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
            $map['idCard'] = $curSheet->getCell('D' . $currentRow)->getValue();
            $map['phone'] = $curSheet->getCell('E' . $currentRow)->getValue();
            $map['email'] = $curSheet->getCell('F' . $currentRow)->getValue();
            $map['_logic'] = 'OR';
            if (empty($map['idCard']) && empty($map['phone']) && empty($map['email'])) {
                $this->error('模板错误!');
                exit;
            }
            $rs = $gc->where($map)->find();
            if (!$rs) {
                $midbody['id'] = $this->crtSinceCode('pr');
                if ($_SESSION['roleId'] != 1)
                    $midbody['volunteerStatus'] = 6;
                else
                    $midbody['volunteerStatus'] = 1;
                $midbody['crttime'] = $this->crtTime();
                for ($currentCol = 'A', $i = 0; $currentCol <= $allColumn; $currentCol++) {
                    $midbody[$feild[$i]] = $curSheet->getCell($currentCol . $currentRow)->getValue();
                    $i++;
                }
               
                array_push($data, $midbody); //超级管理员批量生成数组方便添加到志愿者表
            } else {
                array_push($errData, $map); //错误数据
            }
        }

        if (!empty($data)) {
            $state = $this->Arrdata($data, $teamId);
        }
       
        if(1 == $_SESSION['roleId'] && MODULE_NAME == 'Team'){
        	foreach($errData as $k=>$v){
        		$ids .=  $v['idCard'].',';
        	}
        	$ids = rtrim($ids,',');
        	$gc = M('Volunteer');
       		$teamVolunteer = M('Individual_team');
       		$where['idCard'] = array('in',$ids);
       		$userList = $gc->field('id,email,idCard,volunteerStatus')->where($where)->select();
       		foreach($userList as $k=>$v){
        		if(6 == $v['volunteerStatus']){
        			$gc->where('id="'.$v['id'].'"')->save(array('volunteerStatus'=>1));
        			R('SendMsg/batchSend', array($v['email'], $v['idCard']));//发送邮箱
        		}else{
        			$tv['volunteerId'] = $v['id'];
        			$tv['teamId'] = $teamId;
        			$tv['status'] = 2;
        			$teamVolunteer->where($tv)->delete();
        		 	$teamData['volunteerId'] = $v['id'];
            		//$tId = $teamId ? $teamId : $_SESSION['id'];
            		$teamData['teamId'] = $teamId;
            		$teamData['status'] = 2;
            		$teamVolunteer->add($teamData);	
        		}
        	}
        	//$this->Arrdata($teamData, $teamId);
        	$this->success('导入完成！', U('Team/index'));
        	exit;
        }else{
        	return array($state, $errData);	
        }
        
    }
	//批量添加团队志愿者关系数据
    private function Arrdata($data, $teamId) {
        $gc = M('Volunteer');
        $teamVolunteer = M('Individual_team');
        foreach ($data as $key => $value) {
            $adminData['id'] = $value['id'];
            $adminData['volunteerStatus'] = $value['volunteerStatus'];
            $adminData['crttime'] = $value['crttime'];
            $adminData['name'] = $value['name'];
            $adminData['pwd'] = $value['pwd'];
            $adminData['sex'] = $value['sex'];
            $adminData['idCard'] = $value['idCard'];
            $adminData['phone'] = $value['phone'];
            $adminData['email'] = $value['email'];
            $teamData['volunteerId'] = $value['id'];
            $tId = $teamId ? $teamId : $_SESSION['id'];
            $teamData['teamId'] = $tId;
            $teamData['status'] = 2;
            $map['volunteerId'] = $value['id'];
            $map['teamId'] = $tId;
            $rs = $teamVolunteer->where($map)->find();
            $idCard = preg_match('/^[1-9]\d{5}[1-9]\d{3}((0\d)|(1[0-2]))(([0|1|2]\d)|3[0-1])\d{3}(\d|x|X)$/', $value['idCard']);
            $phone = preg_match('/^(1(([35][0-9])|(47)|[8][01256789]))\d{8}$/', $value['phone']);
            $email = preg_match('/^([0-9A-Za-z\\-_\\.]+)@([0-9a-z]+\\.[a-z]{2,3}(\\.[a-z]{2})?)$/i', $value['email']);
            if ($idCard && $email) {
                if ($_SESSION['roleId'] == 1) {
                    R('SendMsg/batchSend', array($value['email'], $value['idCard']));
                }
                if ($tId) {
                    $state = $teamVolunteer->add($teamData);
                }
                $state = $gc->add($adminData);
            } else {
                $arr[$key]['idCard'] = $value['idCard'];
                $arr[$key]['phone'] = $value['phone'];
                $arr[$key]['email'] = $value['email'];
                $_SESSION['importErrorList_error'] = $arr;
            }
        }

        return $state;
    }

    /**
     * 活动报名导入
     * @param type $path
     * @param type $activityId
     * @return type 数组
     */
    public function activityApplyImport($path = '', $activityId) {
        Vendor('PHPExcel.PHPExcel'); //导入thinkphp第三方类库

        $fType = strrchr($path, '.') == '.xls' ? 'Excel5' : 'Excel2007';
        $objReader = PHPExcel_IOFactory::createReader($fType);

        //设置只读取数据，不包括公式和格式   
        $objReader->setReadDataOnly(true);
        //只读取指定的sheet   
        $objReader->setLoadSheetsOnly('Sheet1');
        $objPHPExcel = $objReader->load($path);

        $curSheet = $objPHPExcel->getSheet(0);
        $setSheet = $objPHPExcel->setActiveSheetIndex(0);

        //包含数据的最大列
        $allColumn = chr(65 + 2);
        //包含数据的最大行  
        $allRow = $curSheet->getHighestRow();
        $data = array();
        $errData = array();
        $gc = M('Volunteer');
        $applyTime = date("Y-m-d H:i:s");
        $apply = M('Apply');

        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
            $map['name'] = $curSheet->getCell('A' . $currentRow)->getValue();
            $map['idCard'] = $curSheet->getCell('B' . $currentRow)->getValue();
            $map['volunteerStatus'] = 2;
            $rs = $gc->where($map)->find();
            if (!empty($rs['id'])) {
                $midbody['activityId'] = $activityId;
                $midbody['volunteerId'] = $rs['id'];
                $midbody['applyTime'] = $applyTime;
                $midbody['applyForm'] = 1; //个人申请
                $midbody['applyStatus'] = 2; //因为管理员导入，所以直接报名成功
                $where['activityId'] = $midbody['activityId'];
                $where['volunteerId'] = $midbody['volunteerId'];
                $applyData = $apply->where($where)->find();

                if (count($applyData)) {//判断是否已报名，且报名是否通过
                    if ($applyData['applyStatus'] != 2)
                        $apply->where($where)->setField('applyStatus', 2);
                } else {
                    array_push($data, $midbody);
                }
            } else {
                array_push($errData, $map); //错误数据
            }
        }

        if (!empty($data)) {
            $state = $apply->addAll($data);
        }
        return array($state, $errData);
    }

    /**
     *  导入志愿者服务时长
     * @param type $path 文件路径
     * @param type $activityId 活动Id
     * @param type $importType  导入类型：1导入开始时间，2导入结束时间
     * @return type 返回结果数组
     */
    public function activityLongImport($path = '', $activityId, $importType) {
        Vendor('PHPExcel.PHPExcel'); //导入thinkphp第三方类库

        $fType = strrchr($path, '.') == '.xls' ? 'Excel5' : 'Excel2007';
        $objReader = PHPExcel_IOFactory::createReader($fType);

        //设置只读取数据，不包括公式和格式   
        $objReader->setReadDataOnly(true);
        //只读取指定的sheet   
        $objReader->setLoadSheetsOnly('sheet1');
        $objPHPExcel = $objReader->load($path);

        $curSheet = $objPHPExcel->getSheet(0);
        $setSheet = $objPHPExcel->setActiveSheetIndex(0);

        //包含数据的最大列
        $allColumn = chr(65 + 2);
        //包含数据的最大行  
        $allRow = $curSheet->getHighestRow();
        $data = array();
        $errData = array();
        $state = 0;
        $gc = M('Volunteer');
        $apply = M('Apply');

        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
            $map['name'] = $curSheet->getCell('A' . $currentRow)->getValue();
            $map['idCard'] = $curSheet->getCell('E' . $currentRow)->getValue();
            $timeDot = $curSheet->getCell('F' . $currentRow)->getValue(); //时间
            $map['volunteerStatus'] = 2;
            $rs = $gc->where($map)->find(); //判断用户是否注册且账号是否正常
            if (!empty($rs['id'])) {
                $where['activityId'] = $activityId;
                $where['volunteerId'] = $rs['id'];
                $where['applyStatus'] = 2;
                $applyData = $apply->where($where)->find();
                if (count($applyData)) {//判断是否已报名，且报名是否通过
                    if (1 == $importType)
                        $uTime['startTime'] = $timeDot;
                    if (2 == $importType) {
                        $uTime['endTime'] = $timeDot;
                        $uTime['serviceLong'] = round((abs(strtotime($timeDot) - strtotime($applyData['startTime'])) / 3600), 1);
                    }

                    //判断是否为管理员导入
                    if ($_SESSION['roleId'] == 1) {
                        $uTime['serviceStatus'] = 2;
                    } else {
                        $uTime['serviceStatus'] = 1;
                    }

                    if ($apply->where($where)->save($uTime)) {
                        $state++;
                    }
                    if ($_SESSION['roleId'] == 1 && 2 == $importType) {
                        unset($map);
                        $map['volunteerId'] = $where['volunteerId'];
                        SyncActivityLong($where['volunteerId'], $apply->where($map)->sum('serviceLong'));
                    }
                } else {
                    array_push($errData, $map);
                }
            } else {
                array_push($errData, $map); //错误数据
            }
        }
        return array($state, $errData);
    }

}

?>
